import sqlite3

class SqliteType(str):
    TEXT = "TEXT"
    INT = "INTEGER"
    PRIMARY_KEY = " PRIMARY KEY "
    AUTOINCREMENT = " AUTOINCREMENT "
    UNIQUE = " UNIQUE "

class SqliteOperator(str):
    EQUALS = "="
    LIKE = "LIKE"

class Statement:
    @staticmethod
    def create_table(table: str, var_list: list[tuple[str, SqliteType]]):
        var_list_str = ""
        for n, t in var_list:
            var_list_str += f"{n} {t}, "
        else:
            var_list_str = var_list_str.rstrip(", ")
        return f'CREATE TABLE IF NOT EXISTS "{table}" ({var_list_str})'
    
    @staticmethod
    def insert_into(table: str, args: list[tuple[str, str]]):
        keys = ""
        vals = ""
        for k, v in args:
            keys += f'"{k}", '
            vals += f'"{v}", '
        else:
            keys = keys.rstrip(", ")
            vals = vals.rstrip(", ")
        return f'INSERT INTO "{table}" ({keys}) VALUES ({vals})'
    
    @staticmethod
    def select_all(table: str):
        return f'SELECT * FROM "{table}"'
    
    @staticmethod
    def select_all_where(table: str, matches: list[tuple[str, SqliteOperator, str]]):
        match_str = ""
        for k, o, m in matches:
            match_str += f'"{k}" {o} "{m}" and '
        else:
            match_str = match_str.rstrip(" and ")
        return f'SELECT * FROM "{table}" WHERE {match_str}'
    
    @staticmethod
    def select_col_where(table: str, col: str, matches: list[tuple[str, SqliteOperator, str]]):
        match_str = ""
        for k, o, m in matches:
            match_str += f'"{k}" {o} "{m}" and '
        else:
            match_str = match_str.rstrip(" and ")
        return f'SELECT "{col}" FROM "{table}" WHERE {match_str}'

class SqliteMain:
    def __init__(self, dbname: str) -> None:
        self.connection = sqlite3.connect(dbname, check_same_thread = False)
        self.cursor = self.connection.cursor()
    
    def create_table(self, table: str, var_list):
        self.cursor.execute(Statement.create_table(table, var_list))
    
    def insert_into(self, table: str, args):
        self.cursor.execute(Statement.insert_into(table, args))
    
    def select_all(self, table: str):
        self.cursor.execute(Statement.select_all(table))
        return self.cursor.fetchall()
    
    def select_all_where(self, table: str, matches):
        self.cursor.execute(Statement.select_all_where(table, matches))
        return self.cursor.fetchall()
    
    def select_col_where(self, table: str, col: str, matches):
        self.cursor.execute(Statement.select_col_where(table, col, matches))
        return self.cursor.fetchall()
    
    def commit(self):
        self.connection.commit()
    
    def close(self):
        self.connection.commit()
        self.cursor.close()
        self.connection.close()

# main = SqliteMain("test.db")

# main.create_table("123", [("id", SqliteType.INT + SqliteType.PRIMARY_KEY + SqliteType.AUTOINCREMENT), ("name", SqliteType.TEXT + SqliteType.UNIQUE), ("md5", SqliteType.TEXT)])
# main.insert_into("123", [("name", "1a"), ("md5", "cf13s4d")])
# main.insert_into("123", [("name", "1b"), ("md5", "cf13s4e")])
# main.insert_into("123", [("name", "1c"), ("md5", "cf13s4f")])
# main.insert_into("123", [("name", "b"), ("md5", "cf13s44")])
# main.insert_into("123", [("name", "c"), ("md5", "cf13s46")])
# print(main.select_all("123"))
# print(main.select_where("123", [("name", SqliteOperator.LIKE, "%b")]))

# main.close()